import pymysql
import os
class MysqlController:
    """将数据库内容进行分类保存"""
    def __init__(self,db_config):
        self.db_config = db_config
        self.conn = None
        self.cur = None

    def creat_db(self):
        self.conn = pymysql.connect(**self.db_config)
        self.cur = self.conn.cursor()

    def commit_db(self,res):
        if res > 0:
            print(f"执行成功: 已处理 {res} 条数据")
        elif res == 0:
            print("没有数据被处理")
        else:
            print("执行失败")
        if self.conn:
            self.conn.commit()
            self.cur.close()
            self.cur = None
            self.conn.close()
            self.conn = None

    def remove_repeat(self):
        """复制所有弹幕,删除重复语句"""
        self.creat_db()
        sql = "INSERT IGNORE INTO input_interaction (comment) SELECT DISTINCT comment FROM all_respond WHERE comment IS NOT NULL;"
        self.commit_db(self.cur.execute(sql))

    def export_interaction(self):
        """导出弹幕到桌面txt文件（按指定范围）"""
        self.creat_db()
        # 获取桌面路径
        desktop_path = os.path.join(os.path.expanduser('~'), 'Desktop')
        output_file = os.path.join(desktop_path, 'exported_comments.txt')
        print(f"弹幕将导出到: {output_file}")
        while True:
            # 让用户选择从哪个表获取数据
            print("\n请选择弹幕来源:")
            print("1. 好评表")
            print("2. 提问表")
            print("3. 忽略表")
            print("4. 导出表")
            print("q. 退出")
            opt = input("请输入选项: ").strip().lower()

            if opt == 'q':
                print("导出操作已取消")
                self.commit_db(0)
                return

            table_mapping = {
                '1': ("input_good", "好评"),
                '2': ("input_question", "提问"),
                '3': ("input_ignore", "忽略"),
                '4': ("exported_comments", "导出")
            }

            if opt not in table_mapping:
                print("无效选择，请重新输入")
                continue

            table_name, table_desc = table_mapping[opt]

            # 获取表的总记录数
            count_sql = f"SELECT COUNT(*) FROM {table_name}"
            self.cur.execute(count_sql)
            total_count = self.cur.fetchone()[0]

            if total_count == 0:
                print(f"{table_desc}表中没有数据!")
                continue

            print(f"\n{table_desc}表共有 {total_count} 条记录")

            # 获取导出范围
            while True:
                try:
                    start = input(f"请输入起始位置 (1-{total_count}, 回车默认1): ").strip()
                    if not start:
                        start = 1
                    else:
                        start = int(start)

                    end = input(f"请输入结束位置 ({start}-{total_count}, 回车默认{total_count}): ").strip()
                    if not end:
                        end = total_count
                    else:
                        end = int(end)

                    # 验证范围有效性
                    if start < 1 or end > total_count or start > end:
                        print(f"范围无效，请确保 1 <= 起始 <= 结束 <= {total_count}")
                        continue

                    break
                except ValueError:
                    print("请输入有效的数字")

            # 计算偏移量和行数
            offset = start - 1
            limit = end - start +1

            # 查询指定范围的数据
            if table_name == "exported_comments":
                select_sql = f"SELECT comment FROM {table_name} ORDER BY id LIMIT {offset}, {limit}"
            else:
                select_sql = f"SELECT comment FROM {table_name} ORDER BY comment LIMIT {offset}, {limit}"

            self.cur.execute(select_sql)
            comments = [row[0] for row in self.cur.fetchall()]

            if not comments:
                print("没有查询到数据!")
                continue

            # 显示将要导出的数据
            print(f"\n===== 即将导出 {len(comments)} 条{table_desc}弹幕 =====")
            for i, comment in enumerate(comments, 1):
                print(f"{i}. {comment}")

            # 确认导出
            confirm = input("\n是否导出这些弹幕到文件? (y: 导出, n: 重新选择, q: 退出): ").strip().lower()

            if confirm == 'q':
                print("导出操作已取消")
                self.commit_db(0)
                return

            if confirm != 'y':
                continue

            # 导出到文件
            try:
                mode = 'a'  # 追加模式
                if not os.path.exists(output_file):
                    mode = 'w'  # 文件不存在则创建

                with open(output_file, mode, encoding='utf-8') as f:
                    for comment in comments:
                        f.write(comment + '\n')

                print(f"成功导出 {len(comments)} 条弹幕到文件: {output_file}")

                # 询问是否继续
                continue_export = input("是否继续导出其他数据? (y: 继续, n: 退出): ").strip().lower()
                if continue_export != 'y':
                    self.commit_db(len(comments))
                    return

            except Exception as e:
                print(f"导出文件时出错: {e}")
                self.commit_db(-1)
                return

    def import_interaction(self):
        """从桌面文件导入弹幕到指定表（可选择范围）"""
        self.creat_db()
        # 获取桌面路径
        desktop_path = os.path.join(os.path.expanduser('~'), 'Desktop')
        input_file = os.path.join(desktop_path, 'imported_comments.txt')
        # 检查文件是否存在
        if not os.path.exists(input_file):
            print(f"错误: 导入文件不存在 - {input_file}")
            self.commit_db(0)
            return
        # 读取文件内容
        try:
            with open(input_file, 'r', encoding='utf-8') as f:
                all_comments = [line.strip() for line in f.readlines() if line.strip()]
        except Exception as e:
            print(f"读取文件时出错: {e}")
            self.commit_db(-1)
            return

        total_lines = len(all_comments)
        if total_lines == 0:
            print("文件内容为空，没有可导入的弹幕")
            self.commit_db(0)
            return
        print(f"找到 {total_lines} 条弹幕")
        # 选择导入范围
        while True:
            try:
                print(f"\n弹幕行号范围: 1 - {total_lines}")
                start_line = input("请输入起始行号 (回车默认1): ").strip()
                end_line = input(f"请输入结束行号 (1-{total_lines}, 回车默认{total_lines}): ").strip()

                # 设置默认值
                start = 1 if not start_line else int(start_line)
                end = total_lines if not end_line else int(end_line)

                # 验证范围有效性
                if start < 1 or end > total_lines or start > end:
                    print(f"范围无效，请确保 1 <= 起始 <= 结束 <= {total_lines}")
                    continue

                # 获取选定范围的弹幕
                selected_comments = all_comments[start - 1:end]
                print(f"\n===== 将要导入 {len(selected_comments)} 条弹幕 =====")
                for i, comment in enumerate(selected_comments, start):
                    print(f"{i}. {comment}")

                break
            except ValueError:
                print("请输入有效的数字")

        # 选择目标表
        while True:
            print("\n请选择导入目标表:")
            print("1. 好评表 (input_good)")
            print("2. 提问表 (input_question)")
            print("3. 忽略表 (input_ignore)")
            print("4. 导出表 (exported_comments)")
            print("q. 取消导入")

            opt = input("请输入选项: ").strip().lower()

            if opt == 'q':
                print("导入操作已取消")
                self.commit_db(0)
                return

            table_mapping = {
                '1': ("input_good", "好评"),
                '2': ("input_question", "提问"),
                '3': ("input_ignore", "忽略"),
                '4': ("exported_comments", "导出")
            }

            if opt not in table_mapping:
                print("无效选择，请重新输入")
                continue

            table_name, table_desc = table_mapping[opt]

            print(f"将导入到 {table_desc} 表")
            break

        # 确认导入
        confirm = input(f"\n是否将 {len(selected_comments)} 条弹幕导入到 {table_desc} 表? (y/n): ").strip().lower()
        if confirm != 'y':
            print("导入操作已取消")
            self.commit_db(0)
            return

        # 执行导入
        try:
            # 使用INSERT IGNORE避免重复
            insert_sql = f"INSERT IGNORE INTO {table_name} (comment) VALUES (%s)"
            result = self.cur.executemany(insert_sql, [(comment,) for comment in selected_comments])
            self.conn.commit()

            print(f"成功导入 {result} 条弹幕到 {table_desc} 表")
            if result < len(selected_comments):
                duplicates = len(selected_comments) - result
                print(f"注意: {duplicates} 条弹幕已存在表中，未重复导入")

            self.commit_db(result)
        except pymysql.Error as e:
            print(f"数据库导入错误: {e}")
            self.conn.rollback()
            self.commit_db(-1)
        except Exception as e:
            print(f"导入过程中出错: {e}")
            self.conn.rollback()
            self.commit_db(-1)

    def _select_interaction(self, target_table, exclude_tables, table_type):
        """通用方法：分页选择弹幕添加到目标表，排除指定表的数据"""
        #实现逻辑有些绕,获取下一页数据时,会修改数据库本身的数据,导致当选择一些数据后,数据顺序会改变(20号数据在选择两个数据后会变为18号,而此时翻页会掠过两个数据)
        #解决办法,偏移量每次增加一页数据条数-选择次数,使每次(总数-选择次数)+增加偏移量 = 一页数据条数(20),也就是两头同时缩减 比较抽象,不好描述
        try:
            self.creat_db()
            # 构建排除条件
            exclude_conditions = " AND ".join([f"comment NOT IN (SELECT comment FROM {table})" for table in exclude_tables]) if exclude_tables else "1=1"
            # 获取待处理记录数
            count_sql = f"SELECT COUNT(*) FROM input_interaction WHERE {exclude_conditions}"
            self.cur.execute(count_sql)
            total_count = self.cur.fetchone()[0]

            if total_count == 0:
                print(f"没有需要处理的记录 - 所有数据已添加到 {target_table} 表!")
                self.commit_db(0)
                return
            
            # 设置分页参数
            page_size = 20
            current_offset = 0
            current_page = 1
            total_pages = (total_count + page_size - 1) // page_size

            #获取全部待处理数据,以检测后面每页之间是否连续,用于测试
            # sq = f"SELECT comment FROM input_interaction WHERE {exclude_conditions} ORDER BY comment"
            # self.cur.execute(sq)
            # page_datas = self.cur.fetchall()
            # for index,data in  enumerate(page_datas,1):
            #     print(index,": ",data)
            #

            print(f"\n共有 {total_count} 条待处理 {table_type} 记录，分为 {total_pages} 页")

            while current_offset < total_count:
                # 获取当前页数据
                select_sql = f"SELECT comment FROM input_interaction WHERE {exclude_conditions} ORDER BY comment LIMIT {current_offset},{page_size}"
                self.cur.execute(select_sql)
                page_data = self.cur.fetchall()
                if not page_data:
                    print("\n没有更多数据")
                    break

                # 显示当前页数据
                print(f"\n===== 第 {current_page} 页 (共 {total_pages} 页) =====")
                for idx, row in enumerate(page_data, 1):
                    print(f"{idx}. {row[0]}")

                # 用户输入处理
                input_number = input("\n请输入要添加的编号 (空格分隔,all全选,q退出): ").strip()
                if input_number.lower() == 'q':
                    print("程序已退出")
                    break
                #评论列表
                comment_list = []
                # 处理全选命令
                if input_number.lower() == 'all':
                    # 全选当前页所有记录
                    comment_list = [row[0] for row in page_data]
                elif input_number:
                    try:
                        # 转换输入为数字列表
                        number_list = [int(num) for num in input_number.split()]
                        for num in number_list:
                            if 1 <= num <= len(page_data):
                                comment_list.append(page_data[num - 1][0])
                            else:
                                print(f"警告: 编号 {num} 无效，已跳过")
                    except ValueError:
                        print("输入格式错误，请只输入数字并用空格分隔")
                        continue

                if comment_list:
                    # 插入到目标表
                    insert_sql = f"INSERT IGNORE INTO {target_table} (comment) VALUES (%s)"
                    res = self.cur.executemany(insert_sql, [(comment,) for comment in comment_list])
                    print(f"成功添加 {res} 条记录到 {target_table} 表")
                    # 更新剩余记录数
                    total_count -= len(comment_list)
                    # 用户选择了记录，获取时减去选择长度
                    current_offset += page_size-len(comment_list)
                    # 提交更改以便后续查询能看到最新数据
                    self.conn.commit()
                else:
                    # 用户没有选择任何记录，正常翻页
                    current_offset += page_size
                current_page += 1
                # 检查是否最后一页
                if current_offset >= total_count:
                    print("\n已处理所有数据")
                    break
            # 提交事务并关闭连接
            self.commit_db(1)  # 使用1表示成功执行

        except pymysql.Error as e:
            print(f"数据库错误: {e}")
            if self.conn:
                self.conn.rollback()
            self.commit_db(-1)  # 使用-1表示失败
        except Exception as e:
            print(f"程序错误: {e}")
            if self.conn:
                self.conn.rollback()
            self.commit_db(-1)  # 使用-1表示失败

    def add_good_interaction(self):
        """选择好评弹幕添加到新表"""
        # 排除已在input_good和input_question表中的数据
        self._select_interaction(
            target_table="input_good",
            exclude_tables=["input_good", "input_question", "input_ignore"],
            table_type="好评弹幕"
        )
    def add_question_interaction(self):
        """选择提问弹幕添加到新表"""
        # 排除已在input_good和input_question表中的数据
        self._select_interaction(
            target_table="input_question",
            exclude_tables=["input_good", "input_question", "input_ignore"],
            table_type="提问弹幕"
        )
    def add_ignore_interaction(self):
        """选择忽略弹幕添加到新表"""
        # 排除已在三个表（好评、提问、忽略）中的数据
        self._select_interaction(
            target_table="input_ignore",
            exclude_tables=["input_good", "input_question", "input_ignore"],
            table_type="忽略弹幕"
        )
    def add_export_interaction(self):
        """选择导出弹幕添加到新表"""
        # 排除已在三个表（导出、忽略）中的数据
        self._select_interaction(
            target_table="exported_comments",
            exclude_tables=["exported_comments", "input_ignore"],
            table_type="导出弹幕"
        )
    def ensure_tables_exist(self):
        """确保所有需要的表都存在"""
        self.creat_db()
        try:
            # 创建input_interaction表（如果不存在）
            res = self.cur.execute("CREATE TABLE IF NOT EXISTS input_interaction (comment VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci PRIMARY KEY)")

            # 创建input_good表（如果不存在）
            res +=self.cur.execute("CREATE TABLE IF NOT EXISTS input_good (comment VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci PRIMARY KEY)")

            # 创建input_question表（如果不存在）
            res += self.cur.execute("CREATE TABLE IF NOT EXISTS input_question (comment VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci PRIMARY KEY)")
            self.commit_db(res)
            print("所有表已准备好")
        except Exception as e:
            print(f"创建表时出错: {e}")
            self.commit_db(-1)
    def central_control(self):
            while True:
                print("\n" + "=" * 50)
                print("1. 开始去重处理")
                print("2. 筛选增加好评")
                print("3. 筛选增加提问")
                print("4. 筛选增加忽略")
                print("5. 筛选增加导出")
                print("6  导出弹幕")
                print("7. 导入弹幕")
                print("8. 退出系统")
                print("=" * 50 + "\n")
                choice = input("请选择操作: ")
                if choice == '1':
                    self.remove_repeat()
                if choice == '2':
                    self.add_good_interaction()
                if choice == '3':
                    self.add_question_interaction()
                if choice == '4':
                    self.add_ignore_interaction()
                if choice == '5':
                    self.add_export_interaction()
                if choice == '6':
                    self.export_interaction()
                if choice == '7':
                    self.import_interaction()
                if choice == '8':
                    return

if __name__ == '__main__':
    db_config={
    "host":"localhost",
    "user":"root",
    "passwd":"root",
    "db":"ointment_interaction",
    "port":3306,
    "charset": "utf8mb4"
    }
    mysql_controller = MysqlController(db_config)
    mysql_controller.central_control()

